package com.action.proceso6;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.bean.proceso6.Contrato;
import com.conexion.DbConnection;

import java.io.InputStream;
import java.io.OutputStream;

public class ContratoAction {

	private final String TABLA = "contrato";

	// metodo para insertar contrato

	public int insertar(Contrato obj) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = DbConnection.getConnection();

			String sql = "INSERT INTO " + TABLA + " VALUES(?, ?, ?, ?, ?, ?)";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, obj.getCodigo());
			pstm.setString(2, obj.getFecha());
			pstm.setInt(3, obj.getIdtipocontrato());
			pstm.setString(4, obj.getIdproveedor());
			pstm.setString(5, obj.getTema());

			FileInputStream fis = new FileInputStream(obj.getArchivo());

			pstm.setBinaryStream(6, fis, obj.getArchivo().length());
			return pstm.executeUpdate();

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	// meto para listar contratos

	public ArrayList<Contrato> listar() {
		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<Contrato> lista = new ArrayList<Contrato>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA;
			pstm = conn.prepareStatement(sql);

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new Contrato(rs.getString(1), rs.getString(2), rs
						.getInt(3), rs.getString(4), rs.getString(5)));
			}

		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

	// metodo para buscar contrato

	public Contrato buscar(String codigo) {

		Connection conn = null;
		PreparedStatement pstm = null;
		Contrato c = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA + " where codigo=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			ResultSet rs = pstm.executeQuery();

			if (rs.next())
				c = new Contrato(rs.getString(1), rs.getString(2),
						rs.getInt(3), rs.getString(4), rs.getString(5));

		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return c;
	}

	public void buscarArchivo(String codigo) {

		Connection conn = null;
		PreparedStatement pstm = null;
		Contrato c = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "Select archivo FROM " + TABLA + " where codigo=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			ResultSet rs = pstm.executeQuery();

			if (rs.next()) {
				InputStream is = rs.getBinaryStream(1);
				String pathFile = System.getProperty("user.dir")
						+ "/templates/reportes/temp/clausulas.pdf";

				saveOutputStream(pathFile, is);
			}
		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static void saveOutputStream(String name, InputStream body) {
		int c;
		try {
			OutputStream f = new FileOutputStream(name);
			while ((c = body.read()) > -1) {
				f.write(c);
			}
			f.close();
		} catch (Exception e) {
			System.err.println("Exception: " + e.getMessage());
			e.printStackTrace();
		}
	}

	// metodo para actualizar el contrato

	public int actualizar1(Contrato obj) {
		Connection conn = null;
		PreparedStatement pstm = null;

		try {

			conn = DbConnection.getConnection();

			String sql = "Update "
					+ TABLA
					+ " set tipocontrato_id=?, proveedor_ruc=?, fecha=?, tema=?, archivo=? where codigo=?";
			pstm = conn.prepareStatement(sql);

			pstm.setInt(1, obj.getIdtipocontrato());
			pstm.setString(2, obj.getIdproveedor());
			pstm.setString(3, obj.getFecha());
			pstm.setString(4, obj.getTema());

			FileInputStream fis = new FileInputStream(obj.getArchivo());

			pstm.setBinaryStream(5, fis, obj.getArchivo().length());
			pstm.setString(6, obj.getCodigo());

			return pstm.executeUpdate();

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public int actualizar2(Contrato obj) {
		Connection conn = null;
		PreparedStatement pstm = null;

		try {

			conn = DbConnection.getConnection();

			String sql = "Update "
					+ TABLA
					+ " set tipocontrato_id=?, proveedor_ruc=?, fecha=?, tema=? where codigo=?";
			pstm = conn.prepareStatement(sql);

			pstm.setInt(1, obj.getIdtipocontrato());
			pstm.setString(2, obj.getIdproveedor());
			pstm.setString(3, obj.getFecha());
			pstm.setString(4, obj.getTema());

			pstm.setString(5, obj.getCodigo());

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	// metodo para eliminar contrato

	public int eliminar(String codigo) {
		Connection conn = null;
		PreparedStatement pstm = null;
		try {

			conn = DbConnection.getConnection();

			String sql = "delete from " + TABLA + " where codigo=?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, codigo);

			return pstm.executeUpdate();

		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return -1;
	}

	public ArrayList<Contrato> listar(String criterio) {
		Connection conn = null;
		PreparedStatement pstm = null;

		ArrayList<Contrato> lista = new ArrayList<Contrato>();
		try {
			conn = DbConnection.getConnection();

			String sql = "Select * FROM " + TABLA + " where tema like ?";
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, "%" + criterio + "%");

			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				lista.add(new Contrato(rs.getString(1), rs.getString(2), rs
						.getInt(3), rs.getString(4), rs.getString(5)));
			}

		} catch (SQLException e) {
			Logger.getLogger(ContratoAction.class.getName()).log(Level.SEVERE,
					null, e);
		} finally {
			try {
				if (pstm != null)
					pstm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lista;
	}

}
